# Copyright (c) 2025, Your Company Pvt. Ltd.
# License: GNU General Public License v3. See license.txt

from collections.abc import Iterator
from operator import itemgetter

import frappe
from frappe import _
from frappe.utils import cint, date_diff, flt, get_datetime, add_months

from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos

Filters = frappe._dict


def execute(filters: Filters = None) -> tuple:
    # 计算三个月前的日期作为判断阈值
    three_months_ago = add_months(filters["to_date"], -3)
    columns = get_columns(filters)
    
    # 获取FIFO库存数据
    item_details = FIFOSlots(filters).generate()
    # 筛选出呆账超过三个月的物料数据
    data = format_report_data(filters, item_details, filters["to_date"], three_months_ago)
    
    # 获取用户选择的轴字段（默认Y轴为物料名称，X轴为呆滞数量）
    y_field = filters.get("y_axis_field") or "item_name"
    x_field = filters.get("x_axis_field") or "stagnant_qty"
    
    # 生成图表数据
    chart_data = get_chart_data(data, filters, y_field, x_field)
    
    return columns, data, None, chart_data


def format_report_data(filters: Filters, item_details: dict, to_date: str, threshold_date: str) -> list[dict]:
    """筛选并格式化库存呆账超过三个月的数据"""
    data = []
    precision = cint(frappe.db.get_single_value("System Settings", "float_precision", cache=True))
    
    for item, item_dict in item_details.items():
        if not flt(item_dict.get("total_qty"), precision):
            continue
        
        details = item_dict["details"]
        fifo_queue = sorted(filter(itemgetter(1), item_dict["fifo_queue"]), key=itemgetter(1))
        
        if not fifo_queue:
            continue
        
        # 计算超过三个月的库存数量
        stagnant_qty = 0.0
        total_stagnant_value = 0.0
        
        for batch in fifo_queue:
            batch_date = batch[1]
            qty = flt(batch[0]) if not item_dict["has_serial_no"] else 1.0
            value = flt(batch[2])
            
            # 检查是否超过三个月
            if date_diff(threshold_date, batch_date) >= 0:
                stagnant_qty += qty
                total_stagnant_value += value
        
        # 只保留有呆账的物料
        if stagnant_qty <= 0:
            continue
        
        # 计算呆账占总库存的比例
        total_qty = flt(item_dict.get("total_qty"), precision)
        stagnant_percentage = (stagnant_qty / total_qty) * 100 if total_qty > 0 else 0
        
        row = [
            details.name,  # 0: item_code
            details.item_name,  # 1: item_name
            details.item_group,  # 2: item_group
            details.brand,  # 3: brand
            total_qty,  # 4: total_qty (不显示仓库时)
            flt(stagnant_qty, precision),  # 5: stagnant_qty (不显示仓库时)
            flt(total_stagnant_value, precision),  # 6: stagnant_value (不显示仓库时)
            flt(stagnant_percentage, 2),  # 7: stagnant_percentage (不显示仓库时)
            details.stock_uom  # 8: uom (不显示仓库时)
        ]
        
        if filters.get("show_warehouse_wise_stock"):
            # 显示仓库时插入仓库字段，调整索引
            row.insert(4, details.warehouse)  # 4: warehouse
            # 此时索引变为：total_qty=5, stagnant_qty=6, stagnant_value=7, percentage=8, uom=9
        
        data.append(row)
    
    # 按呆账数量降序排序
    data.sort(key=lambda x: x[6] if filters.get("show_warehouse_wise_stock") else x[5], reverse=True)
    return data


def get_columns(filters: Filters) -> list[dict]:
    columns = [
        {
            "label": _("Item Code"),
            "fieldname": "item_code",
            "fieldtype": "Link",
            "options": "Item",
            "width": 120,
        },
        {
            "label": _("Item Name"),
            "fieldname": "item_name",
            "fieldtype": "Data",
            "width": 150,
        },
        {
            "label": _("Item Group"),
            "fieldname": "item_group",
            "fieldtype": "Link",
            "options": "Item Group",
            "width": 120,
        },
        {
            "label": _("Brand"),
            "fieldname": "brand",
            "fieldtype": "Link",
            "options": "Brand",
            "width": 100,
        },
    ]
    
    if filters.get("show_warehouse_wise_stock"):
        columns.append({
            "label": _("Warehouse"),
            "fieldname": "warehouse",
            "fieldtype": "Link",
            "options": "Warehouse",
            "width": 120,
        })
    
    columns.extend([
        {
            "label": _("Total Qty"),
            "fieldname": "total_qty",
            "fieldtype": "Float",
            "width": 100,
        },
        {
            "label": _("Stagnant Qty (>3 months)"),
            "fieldname": "stagnant_qty",
            "fieldtype": "Float",
            "width": 150,
        },
        {
            "label": _("Stagnant Value"),
            "fieldname": "stagnant_value",
            "fieldtype": "Float",
            "width": 120,
        },
        {
            "label": _("Stagnant %"),
            "fieldname": "stagnant_percentage",
            "fieldtype": "Percent",
            "width": 100,
        },
        {
            "label": _("UOM"),
            "fieldname": "uom",
            "fieldtype": "Link",
            "options": "UOM",
            "width": 80,
        }
    ])
    
    return columns


def get_chart_data(data: list, filters: Filters, y_field: str, x_field: str) -> dict:
    """
    生成图表数据，支持动态选择Y轴（文本型，如物料名称）和X轴（数值型，如数量）
    """
    if not data:
        return {}
    
    # 定义字段与数据行索引的映射（根据format_report_data的row结构）
    # 基础索引（不显示仓库时）：item_code=0, item_name=1, total_qty=4, stagnant_qty=5
    # 显示仓库时索引偏移1位：total_qty=5, stagnant_qty=6
    show_warehouse = filters.get("show_warehouse_wise_stock")
    field_index_map = {
        "item_name": 1,  # 物料名称（固定索引）
        "item_code": 0,  # 物料编码（固定索引）
        "stagnant_qty": 6 if show_warehouse else 5,  # 呆滞数量
        "total_qty": 5 if show_warehouse else 4,  # 总数量
    }
    
    # 校验字段合法性
    if y_field not in field_index_map or x_field not in field_index_map:
        return {}
    
    # 提取Y轴（类别）和X轴（数值）数据
    y_labels = [row[field_index_map[y_field]] for row in data]
    x_values = [flt(row[field_index_map[x_field]]) for row in data]
    
    # 图表配置
    return {
        "type": "bar",
        "data": {
            "labels": y_labels,  # Y轴：类别（如物料名称）
            "datasets": [
                {
                    "name": _(x_field.replace("_", " ").title()),  # 数据集名称（X轴字段名）
                    "values": x_values  # X轴：数值（如呆滞数量）
                }
            ]
        },
        "options": {
            "is_horizontal": True,  # 横向柱状图（Y轴类别，X轴数值）
            "xAxis": {
                "title": {
                    "text": _(x_field.replace("_", " ").title())  # X轴标题
                }
            },
            "yAxis": {
                "title": {
                    "text": _(y_field.replace("_", " ").title())  # Y轴标题
                },
                "tick": {
                    "style": {
                        "whiteSpace": "normal",  # 允许标签换行
                        "wordBreak": "break-all"
                    }
                }
            },
            "height": 400 + (len(y_labels) * 30),  # 自适应高度
            "width": "100%",
            "barOptions": {
                "stacked": False
            }
        }
    }


class FIFOSlots:
    """复用FIFO库存计算逻辑"""
    
    def __init__(self, filters: dict | None = None, sle: list | None = None):
        self.item_details = {}
        self.transferred_item_details = {}
        self.serial_no_batch_purchase_details = {}
        self.filters = filters
        self.sle = sle

    def generate(self) -> dict:
        # 从正式模块导入（非测试模块）
        from erpnext.stock.doctype.serial_and_batch_bundle.serial_and_batch_bundle import get_serial_nos_from_bundle

        stock_ledger_entries = self.sle
        bundle_wise_serial_nos = frappe._dict({})
        if stock_ledger_entries is None:
            bundle_wise_serial_nos = self.__get_bundle_wise_serial_nos()

        with frappe.db.unbuffered_cursor():
            if stock_ledger_entries is None:
                stock_ledger_entries = self.__get_stock_ledger_entries()

            for d in stock_ledger_entries:
                key, fifo_queue, transferred_item_key = self.__init_key_stores(d)

                if d.voucher_type == "Stock Reconciliation":
                    # 计算实际数量变化
                    prev_balance_qty = self.item_details[key].get("qty_after_transaction", 0)
                    d.actual_qty = flt(d.qty_after_transaction) - flt(prev_balance_qty)

                serial_nos = get_serial_nos(d.serial_no) if d.serial_no else []
                if d.serial_and_batch_bundle and d.has_serial_no:
                    if bundle_wise_serial_nos:
                        serial_nos = bundle_wise_serial_nos.get(d.serial_and_batch_bundle) or []
                    else:
                        serial_nos = get_serial_nos_from_bundle(d.serial_and_batch_bundle) or []

                if d.actual_qty > 0:
                    self.__compute_incoming_stock(d, fifo_queue, transferred_item_key, serial_nos)
                else:
                    self.__compute_outgoing_stock(d, fifo_queue, transferred_item_key, serial_nos)

                self.__update_balances(d, key)

            del stock_ledger_entries

        if not self.filters.get("show_warehouse_wise_stock"):
            # 按物料聚合仓库数据
            self.item_details = self.__aggregate_details_by_item(self.item_details)

        return self.item_details

    def __init_key_stores(self, row: dict) -> tuple:
        """初始化键和FIFO队列"""
        key = (row.name, row.warehouse)
        self.item_details.setdefault(key, {"details": row, "fifo_queue": []})
        fifo_queue = self.item_details[key]["fifo_queue"]

        transferred_item_key = (row.voucher_no, row.name, row.warehouse)
        self.transferred_item_details.setdefault(transferred_item_key, [])

        return key, fifo_queue, transferred_item_key

    def __compute_incoming_stock(self, row: dict, fifo_queue: list, transfer_key: tuple, serial_nos: list):
        """处理入库库存，更新FIFO队列"""
        transfer_data = self.transferred_item_details.get(transfer_key)
        if transfer_data:
            self.__adjust_incoming_transfer_qty(transfer_data, fifo_queue, row)
        else:
            if not serial_nos and not row.get("has_serial_no"):
                if fifo_queue and flt(fifo_queue[0][0]) <= 0:
                    # 中和负库存
                    fifo_queue[0][0] += flt(row.actual_qty)
                    fifo_queue[0][1] = row.posting_date
                    fifo_queue[0][2] += flt(row.stock_value_difference)
                else:
                    fifo_queue.append(
                        [flt(row.actual_qty), row.posting_date, flt(row.stock_value_difference)]
                    )
                return

            valuation = row.stock_value_difference / row.actual_qty if row.actual_qty else 0
            for serial_no in serial_nos:
                if self.serial_no_batch_purchase_details.get(serial_no):
                    fifo_queue.append(
                        [serial_no, self.serial_no_batch_purchase_details.get(serial_no), valuation]
                    )
                else:
                    self.serial_no_batch_purchase_details.setdefault(serial_no, row.posting_date)
                    fifo_queue.append([serial_no, row.posting_date, valuation])

    def __compute_outgoing_stock(self, row: dict, fifo_queue: list, transfer_key: tuple, serial_nos: list):
        """处理出库库存，更新FIFO队列"""
        if serial_nos:
            fifo_queue[:] = [serial_no for serial_no in fifo_queue if serial_no[0] not in serial_nos]
            return

        qty_to_pop = abs(row.actual_qty)
        stock_value = abs(row.stock_value_difference)

        while qty_to_pop:
            slot = fifo_queue[0] if fifo_queue else [0, None, 0]
            if 0 < flt(slot[0]) <= qty_to_pop:
                qty_to_pop -= flt(slot[0])
                stock_value -= flt(slot[2])
                self.transferred_item_details[transfer_key].append(fifo_queue.pop(0))
            elif not fifo_queue:
                fifo_queue.append([-(qty_to_pop), row.posting_date, -(stock_value)])
                self.transferred_item_details[transfer_key].append(
                    [qty_to_pop, row.posting_date, stock_value]
                )
                qty_to_pop = 0
                stock_value = 0
            else:
                slot[0] = flt(slot[0]) - qty_to_pop
                slot[2] = flt(slot[2]) - stock_value
                self.transferred_item_details[transfer_key].append([qty_to_pop, slot[1], stock_value])
                qty_to_pop = 0
                stock_value = 0

    def __adjust_incoming_transfer_qty(self, transfer_data: dict, fifo_queue: list, row: dict):
        """调整转移库存，添加回FIFO队列"""
        transfer_qty_to_pop = flt(row.actual_qty)
        stock_value = flt(row.stock_value_difference)

        def add_to_fifo_queue(slot):
            if fifo_queue and flt(fifo_queue[0][0]) <= 0:
                fifo_queue[0][0] += flt(slot[0])
                fifo_queue[0][1] = slot[1]
                fifo_queue[0][2] += flt(slot[2])
            else:
                fifo_queue.append(slot)

        while transfer_qty_to_pop:
            if transfer_data and 0 < transfer_data[0][0] <= transfer_qty_to_pop:
                transfer_qty_to_pop -= transfer_data[0][0]
                stock_value -= transfer_data[0][2]
                add_to_fifo_queue(transfer_data.pop(0))
            elif not transfer_data:
                add_to_fifo_queue([transfer_qty_to_pop, row.posting_date, stock_value])
                transfer_qty_to_pop = 0
                stock_value = 0
            else:
                transfer_data[0][0] -= transfer_qty_to_pop
                transfer_data[0][2] -= stock_value
                add_to_fifo_queue([transfer_qty_to_pop, transfer_data[0][1], stock_value])
                transfer_qty_to_pop = 0
                stock_value = 0

    def __update_balances(self, row: dict, key: tuple | str):
        """更新库存余额"""
        self.item_details[key]["qty_after_transaction"] = row.qty_after_transaction

        if "total_qty" not in self.item_details[key]:
            self.item_details[key]["total_qty"] = row.actual_qty
        else:
            self.item_details[key]["total_qty"] += row.actual_qty

        self.item_details[key]["has_serial_no"] = row.has_serial_no
        self.item_details[key]["details"].valuation_rate = row.valuation_rate

    def __aggregate_details_by_item(self, wh_wise_data: dict) -> dict:
        """按物料聚合仓库数据"""
        item_aggregated_data = {}
        for key, row in wh_wise_data.items():
            item = key[0]
            if not item_aggregated_data.get(item):
                item_aggregated_data.setdefault(
                    item,
                    {
                        "details": frappe._dict(),
                        "fifo_queue": [],
                        "qty_after_transaction": 0.0,
                        "total_qty": 0.0,
                    },
                )
            item_row = item_aggregated_data.get(item)
            item_row["details"].update(row["details"])
            item_row["fifo_queue"].extend(row["fifo_queue"])
            item_row["qty_after_transaction"] += flt(row["qty_after_transaction"])
            item_row["total_qty"] += flt(row["total_qty"])
            item_row["has_serial_no"] = row["has_serial_no"]

        return item_aggregated_data

    def __get_stock_ledger_entries(self) -> Iterator[dict]:
        """获取库存 ledger 记录"""
        sle = frappe.qb.DocType("Stock Ledger Entry")
        item = self.__get_item_query()
        to_date = get_datetime(self.filters.get("to_date") + " 23:59:59")

        sle_query = (
            frappe.qb.from_(sle)
            .from_(item)
            .select(
                item.name,
                item.item_name,
                item.item_group,
                item.brand,
                item.description,
                item.stock_uom,
                item.has_serial_no,
                item.valuation_method,
                sle.actual_qty,
                sle.stock_value_difference,
                sle.valuation_rate,
                sle.posting_date,
                sle.voucher_type,
                sle.voucher_no,
                sle.serial_no,
                sle.batch_no,
                sle.qty_after_transaction,
                sle.serial_and_batch_bundle,
                sle.warehouse,
            )
            .where(
                (sle.item_code == item.name)
                & (sle.company == self.filters.get("company"))
                & (sle.posting_datetime <= to_date)
                & (sle.is_cancelled != 1)
            )
        )

        if self.filters.get("warehouse"):
            sle_query = self.__get_warehouse_conditions(sle, sle_query)
        elif self.filters.get("warehouse_type"):
            warehouses = frappe.get_all(
                "Warehouse",
                filters={"warehouse_type": self.filters.get("warehouse_type"), "is_group": 0},
                pluck="name",
            )

            if warehouses:
                sle_query = sle_query.where(sle.warehouse.isin(warehouses))

        sle_query = sle_query.orderby(sle.posting_datetime, sle.creation)

        return sle_query.run(as_dict=True, as_iterator=True)

    def __get_bundle_wise_serial_nos(self) -> dict:
        """获取批次/序列号捆绑信息"""
        bundle = frappe.qb.DocType("Serial and Batch Bundle")
        entry = frappe.qb.DocType("Serial and Batch Entry")

        query = (
            frappe.qb.from_(bundle)
            .join(entry)
            .on(bundle.name == entry.parent)
            .select(bundle.name, entry.serial_no)
            .where(
                (bundle.docstatus == 1)
                & (entry.serial_no.isnotnull())
                & (bundle.company == self.filters.get("company"))
                & (bundle.posting_date <= self.filters.get("to_date"))
            )
        )

        for field in ["item_code"]:
            if self.filters.get(field):
                query = query.where(bundle[field] == self.filters.get(field))

        if self.filters.get("warehouse"):
            query = self.__get_warehouse_conditions(bundle, query)

        bundle_wise_serial_nos = frappe._dict({})
        for bundle_name, serial_no in query.run():
            bundle_wise_serial_nos.setdefault(bundle_name, []).append(serial_no)

        return bundle_wise_serial_nos

    def __get_item_query(self) -> str:
        """获取物料查询"""
        item_table = frappe.qb.DocType("Item")

        item = frappe.qb.from_("Item").select(
            "name",
            "item_name",
            "description",
            "stock_uom",
            "brand",
            "item_group",
            "has_serial_no",
            "valuation_method",
        )

        if self.filters.get("item_code"):
            item = item.where(item_table.item_code == self.filters.get("item_code"))

        if self.filters.get("brand"):
            item = item.where(item_table.brand == self.filters.get("brand"))

        return item

    def __get_warehouse_conditions(self, sle, sle_query) -> str:
        """获取仓库条件"""
        warehouse = frappe.qb.DocType("Warehouse")
        lft, rgt = frappe.db.get_value("Warehouse", self.filters.get("warehouse"), ["lft", "rgt"])

        warehouse_results = (
            frappe.qb.from_(warehouse)
            .select("name")
            .where((warehouse.lft >= lft) & (warehouse.rgt <= rgt))
            .run()
        )
        warehouse_results = [x[0] for x in warehouse_results]

        return sle_query.where(sle.warehouse.isin(warehouse_results))